package com.shallowUniverse.dmp.utils;

import com.shallowUniverse.dmp.entity.DbType;
import com.shallowUniverse.dmp.entity.Params;
import com.shallowUniverse.dmp.pojo.DataBase;
import com.shallowUniverse.dmp.pojo.Table;
import com.shallowUniverse.dmp.pojo.TableField;
import com.shallowUniverse.dmp.pojo.Transfer;

import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author Shuai Guo
 * @version 1.0
 * @description
 * @date 2021/8/6
 */
public class SqlUtils {

    public static <T> String createQuery(DataBase dataBase, Table table, List<TableField> tableFields, String keyWord, Params<T> params) {
        //判断数据库类型
        switch (dataBase.getType()) {
            case "mysql":
                return createMysqlQuery(table, tableFields, params);
            case "oracle":
                return createOracleQuery(table, tableFields, params);
            case "sqlServer":
                return createSqlServerQuery(table, tableFields, keyWord, params);
            default:
                return null;
        }
    }

    private static <T> String createMysqlQuery(Table table, List<TableField> tableFields, Params<T> params) {
        //定义返回对象
        StringBuilder builder = new StringBuilder("select ");
        //拼接字段信息
        builder.append(tableFields.stream().map(TableField::getName).collect(Collectors.joining(",")));
        //拼接表名
        builder.append(" from ").append(table.getName());
        //根据条件拼接分页查询
        int size = params.getSize();
        int page = params.getCurrent();
        int start = (page - 1) * size;
        builder.append(" limit ").append(start).append(",").append(size);
        return builder.toString();
    }

    private static <T> String createOracleQuery(Table table, List<TableField> tableFields, Params<T> params) {
        //定义返回对象
        StringBuilder builder = new StringBuilder("select * from (");
        builder.append("select ")
                .append(tableFields.stream().map(TableField::getName).collect(Collectors.joining(",")))
                .append(",rownum r")
                .append(" from ")
                .append(table.getName())
                .append(") result");
        //拿到分页条件
        int size = params.getSize();
        int page = params.getCurrent();
        int start = (page - 1) * size + 1;
        int end = page * size;
        builder.append(" where r between ")
                .append(start)
                .append(" and ")
                .append(end);
        return builder.toString();
    }

    private static <T> String createSqlServerQuery(Table table, List<TableField> tableFields, String keyWord, Params<T> params) {
        //select top 3 * from ArtistModels
        //where ArtistId not in (select top 15 ArtistId from ArtistModels)
        //------where Id not in (select top 条数*页数  ArtistId  from ArtistModels)
        //定义返回对象
        StringBuilder builder = new StringBuilder("select ");
        int size = params.getSize();
        int page = params.getCurrent();
        int end = page * size;
        builder.append("top ")
                .append(size)
                .append(" ")
                .append(tableFields.stream().map(TableField::getName).collect(Collectors.joining(",")))
                .append(" from")
                .append(table.getName())
                .append(" where ")
                .append(keyWord)
                .append(" not in (")
                .append(" select top ")
                .append(end)
                .append(" ")
                .append(keyWord)
                .append(" from")
                .append(table.getName())
                .append(")");
        return builder.toString();
    }

    public static String createQueryById(Table table, List<TableField> tableFields, Transfer transfer, Object value) {
        //定义返回对象
        StringBuilder builder = new StringBuilder("select ");
        builder.append(tableFields.stream().map(TableField::getName).collect(Collectors.joining(",")))
                .append(" from ")
                .append(table.getName())
                .append(" where ")
                .append(transfer.getToSyncField())
                .append(" = '")
                .append(value)
                .append("'");
        return builder.toString();
    }

    public static StringBuilder createInsert(List<TableField> tableFields, Table table) {
        //定义返回对象
        StringBuilder builder = new StringBuilder("insert into ");
        //拼接字段
        builder.append(table.getName())
                .append("(")
                .append(tableFields.stream().map(TableField::getName).collect(Collectors.joining(",")))
                .append(")")
                .append(" values ");
        return builder;
    }

    public static String createInsertValuesBatch(DataBase dataBase, List<TableField> fromFields, List<TableField> toFields, Map<String, Object> values) {
        StringBuilder builder = new StringBuilder("(");
        for (int i = 0; i < toFields.size(); i++) {
            String field = toFields.get(i).getName();
            if (DbType.ORACLE.value.equals(dataBase.getName()) && "date".equals(toFields.get(i).getType())) {
                builder.append("to_date('").append(values.get(fromFields.get(i).getName())).append("'),");
            } else {
                builder.append("'").append(values.get(fromFields.get(i).getName())).append("',");
            }
        }
        String sql = builder.substring(0, builder.length() - 1);
        return sql + "),";
    }

    public static String createUpdate(List<TableField> tableFields, Table table, Transfer transfer) {
        //定义返回对象
        StringBuilder builder = new StringBuilder("update ");
        builder.append(table.getName())
                .append(" ")
                .append(createUpdateValues(tableFields, transfer))
                .append(" where ")
                .append(transfer.getToSyncField())
                .append(" = ?");
        return builder.toString();
    }

    private static String createUpdateValues(List<TableField> tableFields, Transfer transfer) {
        return "set " + tableFields.stream()
                .filter(tableField -> !tableField.getName().equals(transfer.getToSyncField()))
                .map(tableField -> tableField.getName() + " = ?")
                .collect(Collectors.joining(" ,"));
    }

    public static Object[] createInsertValues(DataBase dataBase, List<TableField> tableFields, Map<String, Object> values) {
        tableFields.forEach(tableField -> {
            if (DbType.ORACLE.value.equals(dataBase.getName()) && "date".equals(tableField.getType())) {
                values.put(tableField.getName(), "to_date(" + values.get(tableField.getName()) + ",'yyyy-MM-dd HH:mm:ss'");
            }
        });
        return values.values().toArray();
    }

    public static Object[] createUpdateValues(DataBase dataBase, List<TableField> tableFields, Transfer transfer, Map<String, Object> values) {
        tableFields.forEach(tableField -> {
            if (DbType.ORACLE.value.equals(dataBase.getName()) && "date".equals(tableField.getType())) {
                values.put(tableField.getName(), "to_date(" + values.get(tableField.getName()) + ",'yyyy-MM-dd HH:mm:ss'");
            }
        });
        //添加同步字段值
        values.put("sync", values.get(transfer.getFromSyncField()));
        //去掉同步字段
        values.remove(transfer.getFromSyncField());
        return values.values().toArray();
    }

    public static Object[][] createBatchValues(List<Object[]> values) {
        Object[][] params = new Object[values.size()][];
        for (int i = 0; i < values.size(); i++) {
            params[i] = values.get(i);
        }
        return params;
    }
}
